/*
	========= V$LIBRARYCACHE =========
	Rows with the following NAMESPACE values reflect library cache activity for SQL statements and PL/SQL blocks:
		SQL AREA
		TABLE/PROCEDURE
		BODY
		TRIGGER
	Using the library cache hit ratio formula, the cache hit ratio is the following:
		SUM(PINHITS)/SUM(PINS)
		----------------------
					.999466248
	Note:
	These queries return data from instance startup, rather than statistics gathered during an interval; interval statistics can better pinpoint the problem. See Also: Chapter 6, "Automatic Performance Diagnostics" (http://docs.oracle.com/cd/B28359_01/server.111/b28274/diag.htm#g41683) for information on how gather information over an interval
*/

if exists (select 1
            from  sysobjects
            where  id = object_id('V$LIBRARYCACHE')
            and    type = 'U')
   drop table V$LIBRARYCACHE
go


CREATE TABLE V$LIBRARYCACHE (
	TS			datetime,
	NAMESPACE	varchar(30),
	PINS		int,
	PINHITS		int,
	RELOADS		int,
	INVALIDATIONS	int
)
go

INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -15, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -30, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -45, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -60, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -75, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -90, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -105, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -120, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -135, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -150, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
INSERT INTO V$LIBRARYCACHE VALUES (dateadd(mi, -165, getdate()), 'SQL AREA', 21536413, 21520516, 0, 0)
go
/*
	============== V$SGASTAT ============
	The amount of free memory in the shared pool is reported in V$SGASTAT. Report the current value from this view using the following query:

	SELECT * FROM V$SGASTAT 
	 WHERE NAME = 'free memory'
	   AND POOL = 'shared pool';
*/

if exists (select 1
            from  sysobjects
            where  id = object_id('V$SGASTAT')
            and    type = 'U')
   drop table V$SGASTAT
go
CREATE TABLE V$SGASTAT (
	TS			datetime,
	NAME	varchar(30),
	POOL	varchar(30)
)
go

/*
	============== V$ROWCACHE ============
	$ROWCACHE Columns

	Column	Description
	PARAMETER
		Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.
	GETS
		Shows the total number of requests for information on the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file description data.
	GETMISSES
		Shows the number of data requests which were not satisfied by the cache, requiring an I/O.
	MODIFICATIONS
		Shows the number of times data in the dictionary cache was updated.
		
	Overall dictionary cache hit ratio: SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
	
*/

if exists (select 1
            from  sysobjects
            where  id = object_id('V$ROWCACHE')
            and    type = 'U')
   drop table V$ROWCACHE
go
CREATE TABLE V$ROWCACHE (
	TS			datetime,
	GETS		int,
	GETMISSES	int,
	FIXED		int	
)
go


/*
	============== V$SYSSTAT ============
	
Table 7-1 Statistics for Calculating the Hit Ratio

Statistic	Description
consistent gets from cache
	Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache
	Number of times a CURRENT block was requested from the buffer cache.
physical reads cache
	Total number of data blocks read from disk into buffer cache.

Example 7-1 has been simplified by using values selected directly from the V$SYSSTAT table, rather than over an interval. It is best to calculate the delta of these statistics over an interval while your application is running, then use them to determine the hit ratio.

See Also:
Chapter 6, "Automatic Performance Diagnostics" for more information on collecting statistics over an interval
Example 7-1 Calculating the Buffer Cache Hit Ratio

SELECT NAME, VALUE
  FROM V$SYSSTAT
WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:

1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')

*/

if exists (select 1
            from  sysobjects
            where  id = object_id('V$SYSSTAT')
            and    type = 'U')
   drop table V$SYSSTAT
go
CREATE TABLE V$SYSSTAT (
	NAME	varchar(30),
	VALUE 	int
)
